import sqlite3
import decimal
import sys
import time
import cmath
from datetime import datetime
from datetime import timedelta

db = sqlite3.connect("finance.sqlite")
codes = [
            "000001",
            "002142",
            "600000",
            "600015",
            "600016",
            "600036",
            "601009",
            "601166",
            "601169",
            "601288",
            "601328",
            "601398",
            "601818",
            "601939",
            "601988",
            "601998",
            ]


def findPreReportDate(idate, code):
   # sql = 'select date, loans, badloans, preshares, provision, equity  from report \
   #           where date<="{}" and code="{}" order by date desc  limit 0,1'.format(strtime, code)
    rdate = ""
    sql = 'select date from report \
              where date<="{}" and code="{}" order by date desc  limit 0,1'.format(idate.strftime("%Y-%m-%d"), code)
    ret = db.cursor().execute(sql).fetchall()
    if len(ret) == 1:
        for row in ret:
            rdate = datetime.strptime(ret[0][0], "%Y-%m-%d")
    else:
        print("find prie resportdate error")

    return rdate


def findNxtReportDate(idate, code):
    rdate = ""
    sql = 'select date from report \
              where date>="{}" and code="{}" order by date desc  limit 0,1'.format(idate.strftime("%Y-%m-%d"), code)
    ret = db.cursor().execute(sql).fetchall()

    if len(ret) == 0:
        sql = 'select date from report \
              where date>="{}" and code="{}" order by date asc  limit 0,1'.format((idate-timedelta(days=365)).strftime("%Y-%m-%d"), code)
        ret = db.cursor().execute(sql).fetchall()
        for row in ret:
            rdate = datetime.strptime(ret[0][0], "%Y-%m-%d") + timedelta(days=365)

    elif len(ret) == 1:
        for row in ret:
            rdate = datetime.strptime(ret[0][0], "%Y-%m-%d")

    else:
        print("find nxt reportdate error")

    return rdate


#        loans = row[0]
#        badloans = row[0]
#        preshares = row[0]
#        provision = row[0]
#        equity = row[0]
    
def PredictAE(idate, code):
    preReportdate = findPreReportDate(idate, code)
    lastYpreReportdate = preReportdate - timedelta(days=365)
    lastYReportdate = idate - timedelta(days=365)

    preAE = RportAdjustEquity(preReportdate, code)
    lastYpreAE = RportAdjustEquity(lastYpreReportdate, code)
    
    RAE = (preAE - lastYpreAE)/(lastYpreAE)

    return RportAdjustEquity(lastYReportdate, code) * (1+RAE)



    return 0

def RportAdjustEquity(idate, code):
    sql = 'select date, loans, badloans, preshares, provision, equity  from report \
             where date="{}" and code="{}"'.format(idate.strftime("%Y-%m-%d"), code)
    ret = db.cursor().execute(sql).fetchall()
    if len(ret) == 0:
       return PredictAE(idate, code) 

    elif len(ret) == 1:
       loans = ret[0][1]
       badloans = ret[0][2]
       preshares = ret[0][3]
       provision = ret[0][4]
       equity = ret[0][5]

#       print( "idate:", str(idate), "equity:", equity, " provision: ", provision, " max: ", decimal.Decimal.max(decimal.Decimal(loans * 0.025), decimal.Decimal(badloans * 1.5)), " AE:", float(equity - preshares + provision - decimal.Decimal.max(decimal.Decimal(loans * 0.025), decimal.Decimal(badloans * 1.5)))
       return float(equity - preshares + provision - decimal.Decimal.max(decimal.Decimal(loans * 0.025), decimal.Decimal(badloans * 1.5)))



def FanBeiQi(indexdate, code, price, shareCount):
    preReportdate = findPreReportDate(indexdate, code)
    nxtReportdate = findNxtReportDate(indexdate, code)
    lastYpreReportdate = preReportdate - timedelta(days=365)

####
    preAE = RportAdjustEquity(preReportdate, code)
    nxtAE = RportAdjustEquity(nxtReportdate, code)
    lastYpreAE = RportAdjustEquity(lastYpreReportdate, code)

    print(indexdate)
    print(preReportdate)
    print(nxtReportdate)
    todayAE = preAE + (nxtAE - preAE) * (indexdate-preReportdate).days / float((nxtReportdate - preReportdate).days)
    s_todayAE = todayAE / shareCount

#    RAE = (preAE - lastYpreAE)/(lastYpreAE)
#    RAE = (nxtAE - preAE)/preAE
    RAE = 0.1393
    if RAE < 0:
        return 0
    print("RAE:", RAE)
    print("price:", price)
    print("s_todayAE:", s_todayAE)
    return cmath.log(price*2/s_todayAE, (1+RAE))


if __name__ == "__main__":
    print(FanBeiQi(datetime.strptime("2017-12-01", "%Y-%m-%d"), "600016", 8.83, 36485.34875)
#    print(  FanBeiQi(datetime.strptime("2017-12-01", "%Y-%m-%d"), "600000", 12.91, 29352.0804)
    #print RportAdjustEquity(datetime.strptime("2017-12-31", "%Y-%m-%d"), "600000")
    #print RportAdjustEquity(datetime.strptime("2017-09-30", "%Y-%m-%d"), "600000")
          )